/**
 * @Date:   2018-09-20T14:18:51+08:00
 * @Filename: mysql.js
 * @Last modified time: 2018-09-23T01:55:01+08:00
 */

var mysql = require('mysql');
let config = require('../config').database

class Mysql {

  constructor() {
    if (new.target !== undefined) {
      this.__MODULE__ = config.PREFIX + new.target.name.toLowerCase()
    } else {
      throw new Error('必须使用 new 命令生成实例')
    }
    this.config = config
    this.getConnection()
    this._pk = 'id'
    this._field = '*'
    this._debug = false
    this._autoTimestampFiled = 'create_date'
  }

  set pk(pk) {
    this._pk = pk
  }

  set field(field) {
    this._field = field
  }

  set debug(debug) {
    this._debug = debug
  }

  set autoTimestampFiled (timestamp) {
    this._autoTimestampFiled = timestamp
  }

  getConnection() {
    let connection = mysql.createConnection({
      host: config.HOST,
      user: config.USERNAME,
      password: config.PASSWORD,
      database: config.DATABASE,
      port: config.PORT,
      multipleStatements: true
    })
    connection.config.queryFormat = function(query, values) {
      if (!values) return query
      return query.replace(/\:(\w+)/g, function(txt, key) {
        if (values.hasOwnProperty(key)) {
          return this.escape(values[key])
        }
        return txt
      }.bind(this))
    }
    connection.connect(err => {
      if (err) throw err
      console.log('connected as id ' + connection.threadId);
    })
    connection.ping(err => {
      if (err) throw err
      console.log('Server responded to ping')
    })
    this.conn = connection
  }

  getPoolConnection() {
    return new Promise((resolve, reject) => {
      let pool = mysql.createPool({
        connectionLimit: 10,
        host: config.HOST,
        user: config.USERNAME,
        password: config.PASSWORD,
        database: config.DATABASE,
        multipleStatements: true
      })
      pool.getConnection((err, connection) => {
        if (err) reject(err) // not connected!
        // Use the connection
        resolve(connection)
      })
    })
  }

  distroy() {
    this.conn.destroy()
  }

  field (field = '') {
    if(field){
      if (field != '*' && typeof(field) === 'string') {
        field = field.split(',')
        this._field = field
      }else{
        this._field = field
      }
    }
  }

  /**
   * [select 查询]
   * @param  {String} [where='']  [description]
   * @param  {String} [filed='*'] [description]
   * @return {[type]}             [description]
   */
  select(where = '', filed = '') {
    where = this.where(where)
    this.field(field)
    let sql = `SELECT ${this._field} FROM ?? ${where}`
    sql = mysql.format(sql, [this.__MODULE__])
    if (this._debug) console.log('select sql', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        resolve(results)
      })
    })
  }

  selectByFiled(param) {
    let value = null
    let key = ''
    for (key in param) {
      if (param.hasOwnProperty(key)) {
        value = param[key]
      }
    }
    let sql = `SELECT ${this._field} FROM ?? WHERE ?? = ?`
    sql = mysql.format(sql, [this.__MODULE__,key,value])
    if (this._debug) console.log('selectByFiled sql', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        resolve(results)
      })
    })
  }

  /**
   * [save 保存]
   * @param  {[type]} params [description]
   * @return {[type]}        [description]
   */
  save(params) {
    params = _params.call(this, params)
    let sql = `INSERT INTO ?? (${params.columns + ', ??'}) VALUES (${params.values + ',?'})`
    sql = mysql.format(sql,[this.__MODULE__,this._autoTimestampFiled,mysql.raw('CURRENT_TIMESTAMP()')])
    if (this._debug) console.log('save sql: ', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        resolve(results.insertId)
      })
    })
  }

  /**
   * [saveAll 保存所有]
   * @param  {[type]} params [description]
   * @return {[type]}        [description]
   */
  saveAll (params) {
    params = _params.call(this, params)
    let sql = `INSERT INTO ${this.__MODULE__} (${params.columns}) VALUES (${params.values})`
    if (this._debug) console.log('saveAll sql: ', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        resolve(results.affectedRows)
      })
    })
  }

  find (id = 0, field= '', pk = '') {
    this._pk = pk || this._pk
    this.field(field)
    let sql = `SELECT ${this._field} FROM ?? WHERE ${this._pk} = ${id}`
    sql = mysql.format(sql, [this.__MODULE__])
    if (this._debug) console.log('find sql: ', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        if (!results) {
          reject('has no data')
          return
        }
        resolve(results[0])
      })
    })
  }

  /**
   * [delete 删除]
   * @param  {[type]} where [description]
   * @return {[type]}       [description]
   */
  delete(where) {
    where = this.where(where)
    let sql = `DELETE FROM ?? ${where}`
    sql = mysql.format(sql,[this.__MODULE__])
    if (this._debug) console.log('delete sql', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql,(error, results, fields) => {
        if (error) reject(error)
        resolve(results.affectedRows)
      })
    })
  }

  deleteOne(id = 0) {
    let sql = `DELETE FROM ?? where ${this._pk} = ?`
    sql = mysql.format(sql,[this.__MODULE__, id])
    if (this._debug) console.log('deleteOne sql', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql,(error, results, fields) => {
        if (error) reject(error)
        resolve(results.affectedRows)
      })
    })
  }

  /**
   * [count 计数]
   * @param  {String} [where='']  [description]
   * @param  {String} [field='*'] [description]
   * @return {[type]}             [description]
   */
  count(where = '') {
    where = this.where(where)
    let sql = `SELECT count('*') AS 'count' FROM ?? ??`
    let inserts = [this.__MODULE__, where]
    sql = mysql.format(sql, inserts)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        resolve(results[0].count)
      })
    })
  }
  /**
   * [update 更新数据]
   * @param  {[type]} params [description]
   * @param  {[type]} where  [description]
   * @return {[type]}        [description]
   */
  update(params, where) {
    where = this.where(where)
    let _param = _params.call(this, params)
    let columns = _param.columns.split(',')
    let _column = ''
    for (var key in columns) {
      if (columns.hasOwnProperty(key)) {
        _column += `, ${columns[key]} = :${columns[key].replace(/`/g,'')}`
      }
    }
    _column += `, ?? =  ?`
    let sql = `UPDATE ?? SET ${_column.substring(1)} ${where}`
    sql = mysql.format(sql, [this.__MODULE__,this._autoTimestampFiled,mysql.raw('CURRENT_TIMESTAMP()')])
    if (this._debug) console.log('update: ', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, params, (error, results, fields) => {
        if (error) reject(error)
        resolve(results.changedRows)
      })
    })
  }
  /**
   * [updateByField 根据字段更新]
   * @param  {[type]} params [description]
   * @return {[type]}        [description]
   */
  updateByField(params) {
    let value = null
    let key = []
    let _pk = ''
    let _w = null
    for (key in params) {
      if (params.hasOwnProperty(key)) {
        if (key === this._pk) {
          _pk = key
          _w = params[key]
        } else {
          value = params[key]
        }
      }
    }
    let sql = _pk ? `UPDATE ?? SET ?? = ?,?? = ? WHERE ${_pk} = ${_w}` : `UPDATE ?? SET ?? = ?,?? = ?`
    sql = mysql.format(sql, [this.__MODULE__,key,value,this._autoTimestampFiled,mysql.raw('CURRENT_TIMESTAMP()')])
    if (this._debug) console.log('updateByField sql', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        resolve(results.changedRows)
      })
    })
  }
  /**
   * [pagination 分页]
   * @param  {Number} [page=1]    [description]
   * @param  {Number} [size=2]    [description]
   * @param  {String} [where='']  [description]
   * @param  {String} [field='*'] [description]
   * @return {[type]}             [description]
   */
  pagination(page = 1, size = 2, where = '', field = '*') {
    where = this.where(where)
    let limit = `${(page-1)*size},${size}`
    if (field != '*' && typeof(field) === 'string') {
      field = field.split(',')
    }
    let sql = 'SELECT ?? FROM ?? ' + where + ' limit ' + limit + ' ; SELECT count("*") as count FROM ?? ' + where
    sql = mysql.format(sql, [field, this.__MODULE__, this.__MODULE__])
    if (this._debug) console.log('pagination sql: ', sql)
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        let _data = JSON.parse(JSON.stringify(results))
        let list = _data[0]
        let totals = _data[1][0].count
        let totalsPage = (totals + size - 1) / size
        let data = {
          currentPage: page,
          pageSize: size,
          totalsNumber: totals,
          totalsPage: totalsPage,
          dataList: list
        }
        resolve(data)
      })
    })
  }
  /**
   * [query 执行一个sql]
   * @param  {[type]} sql [description]
   * @return {[type]}     [description]
   */
  query(sql) {
    return new Promise((resolve, reject) => {
      this.conn.query(sql, (error, results, fields) => {
        if (error) reject(error)
        resolve(results)
      })
    })
  }
  /**
   * [transaction 启动一个事物]
   * @return {[type]} [description]
   */
  transaction() {
    // rollback
    return new Promise((resolve, reject) => {
      let trans = this.conn.beginTransaction(err => {
        if (err) {
          reject(err)
        }
      })
      resolve(trans)
    })
  }

  /**
   * [where 组织条件]
   * @param  {[type]} where [description]
   * @return {[type]}       [description]
   */
  where(where) {
    if (!where) return ''
    if (typeof(where) === 'string') {
      return where
    } else if (where.constructor === Array) {
      let _where = 'WHERE '
      if (where.length != 3) {
        // [['id','eq',12],['title','eq','测试', 'and']] where `id` = 12 and `title` = '测试
        // [{filed: 'title', op: 'eq', value: '测试'},{filed: 'id', op: 'gt', value: 12,_op:'or'}] where `id` > 12 or `title` = '测试'
        for (var i = 0; i < where.length; i++) {
          if (where[i].constructor === Array) {
            let __op = ''
            if (i > 0) {
              if (where[i][3]) {
                __op = `${where[i][3]}`
              } else {
                __op = 'and'
              }
            }
            _where += _symbol.call(this, __op) + ' `' + where[i][0] + '`' + _symbol.call(this, where[i][1]) + where[i][2]
          }
          if (where[i].constructor === Object) {
            let __op = where._op || ' and '
            _where += _symbol.call(this, __op) + ' `' + where.field + '`' + _symbol.call(this, where.op) + ' ' + where.value
          }
        }
      } else if (where.length === 3 && where.constructor === Array) {
        // ['title','eq','测试'] where `title` = '测试'
        _where = 'WHERE `' + where[0] + '` ' + _symbol.call(this, where[1]) + ' ' + where[2]
      } else if (where.length === 3 && where.constructor === Object) {
        // {filed: 'title', op: 'eq', value: '测试'} where `title` = '测试'
        _where = 'WHERE ' + '`' + where.field + '`' + _symbol.call(this, where.op) + ' ' + where.value
      }
      return _where
    }
  }

  /**
   * [condition 组织条件]
   * @param  {[type]} params [description]
   * @return {[type]}        [description]
   */
  condition(params) {
    if (params.constructor === Array) {
      let [where, field, limit, order, group] = params
      where = this.where(where)
      if (field != '*' && typeof(field) === 'string') {
        field = field.split(',')
      }
      return {
        where: where,
        field: field,
        limit: limit,
        order: order,
        group: group
      }
    } else {
      let {
        where,
        field,
        limit,
        order,
        group
      } = params
      where = this.where(where)
      if (field != '*' && typeof(field) === 'string') {
        field = field.split(',')
      }
      return {
        where: where,
        field: field,
        limit: limit,
        order: order,
        group: group
      }
    }
  }
}

/**
 * [_params 拆分参数]
 * @param       {[type]} params [description]
 * @constructor
 * @return      {[type]}        [description]
 */
function _params(params) {
  let columns = ''
  let values = ''
  if (params.constructor === Array) {
    for (const key in params[0]) {
      if (params[0][key]) {
        columns += ',`' + key + '`'
      }
    }
    for (var key in params) {
      if (params.hasOwnProperty(key)) {
        values += "("
        let i = 0
        for (var item in params[key]) {
          if (params[key].hasOwnProperty(item)) {
            if (i === 0){
              values += "'" + params[key][item] + "'"
            } else {
              values += ",'" + params[key][item] + "'"
            }
            i++
          }
        }
        values += "),"
      }
    }
    values = values.substring(0, values.length - 2)
  }else {
    for (const key in params) {
      if (params[key]) {
        columns += ',`' + key + '`'
        values += ",'" + params[key] + "'"
      }
    }
  }
  return {
    columns: columns.substring(1),
    values: values.substring(1)
  }
}

/**
 * [_symbol 映射操作符]
 * @param       {[type]} op [description]
 * @constructor
 * @return      {[type]}    [description]
 */
function _symbol(op) {
  if (!op) return ''
  let _op = {
    'eq': ' = ',
    'neq': ' <> ',
    'gt': ' > ',
    'lt': ' < ',
    'between': ' between ',
    'and': ' and ',
    'like': ' like ',
    'or': ' or ',
    'leq': ' <= ',
    'geq': ' >= '
  }
  return _op[op] || op
}

module.exports = Mysql
